﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Beeldbankmvc.ViewModels;
using System.Configuration;

namespace Beeldbankmvc.Models
{
    public class GebiedRepository
    {
        BeeldbankLinqDataContext db = new BeeldbankLinqDataContext();

        public IEnumerable<IGrouping<int, BEELDBANK_GEBIED_INDEX>> getAvailableYearsFromView(int MapId)
        {
            try
            {
                var availableYears = from p in db.ExecuteQuery<BEELDBANK_GEBIED_INDEX>("SELECT * FROM [BEELDBANK_GEBIED_" + MapId + "]", new object[] { }).ToList()
                                     orderby p.Datum_Realisatie.Value.Year
                                     group p by p.Datum_Realisatie.Value.Year into g
                                     select g;
                return availableYears;
            }
            catch
            {
                var empty = new BEELDBANK_GEBIED_INDEX[]{};
                var availableYears = empty.GroupBy(x => x.Id);
                return availableYears;
            }
        }

        public IEnumerable<IGrouping<int, BEELDBANK_MEDIAOBJECT>> getAvailableYearsFromMediaObjects(double topLeftLatitude, double topLeftLongitude, double topRightLatitude, double topRightLongitude)
        {
            var availableYears = from p in db.BEELDBANK_MEDIAOBJECTs
                                 where
                                    p.Latitude <= topLeftLatitude && p.Latitude >= topRightLatitude &&
                                    p.Longitude >= topLeftLongitude && p.Longitude <= topRightLongitude &&
                                    p.StatusId == Convert.ToUInt32(ConfigurationManager.AppSettings["publishedExternal"])
                                 orderby
                                    p.Datum_Realisatie.Value.Year
                                 group
                                    p by p.Datum_Realisatie.Value.Year into g
                                 select g;
            return availableYears;
        }

        public IEnumerable<BEELDBANK_GEBIED_INDEX> getLocationsFromView(int MapId, DateTime? realisatieFrom, DateTime? realisatieTo)
        {
            try
            {
                var locations = from p in db.ExecuteQuery<BEELDBANK_GEBIED_INDEX>("SELECT * FROM [BEELDBANK_GEBIED_" + MapId + "]", new object[] { }).ToList()
                                where
                                    p.Datum_Realisatie >= realisatieFrom && p.Datum_Realisatie < realisatieTo
                                select p;

                return locations;
            }
            catch
            {
                var locations = new BEELDBANK_GEBIED_INDEX[]{};
                return locations;
            }
        }

        public IEnumerable<BEELDBANK_MEDIAOBJECT> getReferenceLocationsFromMediaObjects(double topLeftLatitude, double topLeftLongitude, double topRightLatitude, double topRightLongitude, DateTime? realisatieFrom, DateTime? realisatieTo)
        {
            var locations = from p in db.BEELDBANK_MEDIAOBJECTs
                            where
                                p.StatusId == Convert.ToUInt32(ConfigurationManager.AppSettings["hidden"]) &&
                                p.Latitude <= topLeftLatitude && p.Latitude >= topRightLatitude &&
                                p.Longitude >= topLeftLongitude && p.Longitude <= topRightLongitude
                            select p;

            return locations;
        }

        public IEnumerable<BEELDBANK_MEDIAOBJECT> getLocationsFromMediaObjects(double topLeftLatitude, double topLeftLongitude, double topRightLatitude, double topRightLongitude, DateTime? realisatieFrom, DateTime? realisatieTo)
        {
            var locations = from p in db.BEELDBANK_MEDIAOBJECTs
                            where
                                p.StatusId == Convert.ToUInt32(ConfigurationManager.AppSettings["publishedExternal"]) &&
                                p.Latitude <= topLeftLatitude && p.Latitude >= topRightLatitude &&
                                p.Longitude >= topLeftLongitude && p.Longitude <= topRightLongitude &&
                                p.Datum_Realisatie >= realisatieFrom && p.Datum_Realisatie < realisatieTo
                            select p;
     
            return locations;
        }

        public IEnumerable<BEELDBANK_GEBIED_INDEX> reduceLocationsFromView(IEnumerable<BEELDBANK_GEBIED_INDEX> locations, int skipNubmer)
        {
            locations = from p in locations
                        where
                            p.Id % skipNubmer == 0
                        select p;
            return locations;
        }

        public IEnumerable<BEELDBANK_MEDIAOBJECT> reduceLocationsFromMediaObjects(IEnumerable<BEELDBANK_MEDIAOBJECT> locations, int skipNubmer)
        {
            locations = from p in locations
                        where
                            p.Id % skipNubmer == 0
                        select p;
            return locations;
        }

        public IQueryable<BEELDBANK_GEBIED> FindAll()
        {
            return db.BEELDBANK_GEBIEDs;
        }

        public int MapByLocation(double Latitude, double Longitude)
        {
            var gebied = db.BEELDBANK_GEBIEDs.Where(p => p.linksBovenLatitude >= Latitude && p.rechtsOnderLatitude <= Latitude && p.linksBovenLongitude <= Longitude && p.rechtsOnderLongitude >= Longitude && p.Naam != "Nederland").FirstOrDefault();
            if (gebied != null)
                return gebied.Id;
            else
                return 0;
        }

        public BEELDBANK_GEBIED FindOne(int id)
        {
            return db.BEELDBANK_GEBIEDs.SingleOrDefault(p => p.Id == id);
        }
    }
}